/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package IFSv2.Util;

import IFSv2.Connection.DBConnection;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

/**
 *
 * @author Fulbert
 */
public class utilData {
    
        public utilData(){}

        public enum SQLDataType
        {
            VARCHAR,VARBINARY,DATETIME,BOOLEAN,
            CHAR,ENUM,FLOAT,INT,TEXT,BOOL,BIT,
            BIGINT, BINARY, BLOB, DATE,
            DECIMAL, DOUBLE, LONGBLOB, LONGTEXT,
            MEDIUMBLOB, MEDIUMINT, MEDIUMTEXT, 
            NUMBERIC, REAL, SET, SMALLINT, TIME,
            TIMESTAMP, TINYBLOB, TINYINT, TINYTEXT,
            YEAR, INT_UNSIGNED
        }

        public static String EscapeString(String str, String esc)
        {
            String _return = "";

            if (str != null)
                _return = str.replace(esc, "\\" + esc);
            
            return _return;
        }

        public static String FormatData(String strRaw, String strDataType)
        {
            String _return;
            
            switch(SQLDataType.valueOf(strDataType.toUpperCase().replace(' ','_')))
            {
                case BOOL:
                case BIT:
                    _return = SQLFormatBitVal(strRaw);
                    break;

                case DATETIME:
                case DATE:
                    _return = SQLFormatDateTimeVal(strRaw);
                    break;
                
                case INT: 
                case TINYINT:
                case FLOAT:
                case DECIMAL:
                    _return = SQLFormatNumVal(strRaw);
                    break;
                    
                case VARCHAR:
                case TEXT:       
                default:
                    _return = SQLFormatTextVal(strRaw);
                    break;

            }

            return _return;
        }

        public static String SQLFormatBitVal(String strRaw)
        {
            String _return;
            String strVal = "";

//            if (!strRaw.equals(""))
//                strVal = strRaw.substring(0, 1);
            
            if (strVal.length() == 0)
                _return = "0";
            else if (strVal.compareTo("0") == 0)
                _return = "0";
            else if (strVal.compareTo("F") == 0)
                _return = "0";
            else if (strVal.compareTo("f") == 0)
                _return = "0";
            else
                _return = "1";
            
            return _return;
        }

        public static String SQLFormatTextVal(String strRaw)
        {
            String _return;
            int intLen = 0;

            if (strRaw != null)
                intLen = strRaw.length();
            
            String strVal = EscapeString(strRaw, "'");

            if (intLen > 0)
                _return = "'" + strVal + "'";
            else
                _return = "null";

            return _return;
        }

        public static String SQLFormatNumVal(String strRaw)
        {
            String _return;
            int intLen = 0;

            if (strRaw != null)
                intLen = strRaw.length();

            if (intLen < 1)
                _return = "null";
            else
                strRaw = strRaw.replace(",","").replace("(", "").replace(")", "");
                _return = strRaw;

            return _return;
        }

        public static String SQLFormatDateTimeVal(String strRaw)
        {
            String _return = "";
            int intLen = 0;           
            
            if (strRaw != null)
                intLen = strRaw.length();
            
            if (intLen < 1 )
                _return = "null";
            else{

                if (strRaw.equals("NOW()"))
                    _return = "NOW()";
                else{
                    try{
                        _return = "STR_TO_DATE('" + strRaw + "', '%m/%d/%Y')";
                    } catch(Exception e){}
                   
                }
            }  
            return _return;
        }

        public static String SQLFormatCheckVal(boolean chk, String strCheckedVal, String strUnCheckedVal)
        {
            strCheckedVal = "true";
            strUnCheckedVal = "false";
            
            if (chk == true)
                return strCheckedVal;
            else
                return strUnCheckedVal;
        }

        public static Boolean CheckValue(String strValue)
        {
            boolean _return = true;
            int intLen = 0;

            if (strValue != null)
                intLen = strValue.length();

            if (intLen < 1)
                _return = false;
            else{

                if ("0".equals(strValue))
                    _return = false;
            }
            return _return;
        }
               
        public static int GetNextVal(String table, String column)
        {
            
            int _return = 0;
            ResultSet result;
            try
            {
                String _sql = "SELECT " + column + " +1 AS "+ column +" FROM " + table + " ORDER BY  " + column + " DESC LIMIT 1";
                
                result = DBConnection.getActiveConnection().createStatement().executeQuery(_sql);
                result.next();
                _return = result.getInt(column);
                result.close();
                return _return;
            }
            catch (Exception e){
            }
            return _return;
        }

        public static String getDescription(String tableName, String colID, String colDesc, Object data){
        
            String _return = "";
            
            try {
                String _sql = "SELECT "+colDesc+" FROM "+tableName+" WHERE "+colID+" = ?";
                PreparedStatement ps = DBConnection.getActiveConnection().prepareStatement(_sql);
                ps.setString(1, data.toString());
                try (ResultSet rsData = ps.executeQuery()) {
                    rsData.next();
                    _return = rsData.getString(colDesc);
                }
                return _return;

            } catch (Exception ex) {
                return _return;
            }
            
        }

       
        public static String getID(String tableName, String colID, String colDesc, Object data){
        
            String _return = "";
            try {
                String _sql = "SELECT "+colID+" FROM "+tableName+" WHERE "+colDesc+" = ?";
                PreparedStatement ps = DBConnection.getActiveConnection().prepareStatement(_sql);
                ps.setString(1, data.toString());
                try (ResultSet rsData = ps.executeQuery()) {
                    rsData.next();
                    _return = rsData.getString(colID);
                }
                return _return;

            } catch (Exception ex) {
                return _return;
            }
            
        }

        public static String formatDate(String date){
            if("null".equals(date) || date.isEmpty()){
                return null;
            } else{    
                String year = date.substring(0,4);
                date = (date.substring(5,10)+"/"+year).replace('-','/');
                return date;
            }
        }
}
